MySQL迁移到PostgreSQL |
您所在的位置：网站首页 › mysql 迁移到sqlserver › MySQL迁移到PostgreSQL |
|
MySQL迁移到PostgreSQL 本文用于指引将数据库从MySQL迁移到PostgreSQL。 1. 数据库迁移1.1 database与schema的选择MySQL和PG的概念区别： 在MySQL中，database与schema是等同的概念，database与database之间可以关联访问在PostgreSQL中，一个database可以包含多个schema,database与database之间不能关联访问，schema与schema之间可以关联访问建议从MySQL迁移到PostgreSQL时: 每个MySQL Database对应PG新建一个DatabasePG侧根据应用名建立专用schema和专用用户，将专用用户的默认schema设置为应用专用schema-- 查询默认schemaSHOW search_path;-- 创建schema并设置用户默认schemaCREATE SCHEMA my_schema;GRANT ALL ON SCHEMA my_schema TO my_user;-- 连接级别设置默认schemaSET search_path TO my_schema;-- 数据库级别设置默认schemaALTER database "my_database" SET search_path TO my_schema;在不明确自己是否需要的情况下，不使用多schema特性，保持迁移前后逻辑统一若代码中使用了select * from database.table这种语法，需将database前缀去掉，因为在PG中，这种语法会去访问其他schema，导致找不到表 1.2 数据割接根据PostgreSQL和MySQL的语法异同，重新组织DDL，在目标PostgresSQL建库建表 主要语法区别包括字段自增、字段注释、建索引、字段类型转换等 使用数据同步与迁移工具提供的MySQL->PostgreSQL数据迁移工具，将数据从MySQL迁移到PostgreSQL 2. 研发与使用差异2.1 命名规则差异【强制】库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。 【强制】对象名（表名、列名、函数名、视图名、序列名等对象名称）规范，对象名务必只使用小写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使用保留字。保留字参考https://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html 【强制】query中的别名不要使用 “小写字母，下划线，数字” 以外的字符，例如中文。 参考：http://120.76.251.97:8181/docs/pgbook/pgbook-1c1ajj5u77j6t 2.2 常用DML语法差异mysqlpostgresql说明#–mysql的单行注释使用#，pg使用–‘ “ vs. `‘vs. “mysql使用单引号或双引号代表字符串，pg只接受单引号作为字符串；mysql使用`标识表名、列名，pg使用双引号… WHERE lastname=”smith”… WHERE lower(lastname)=’smith’mysql字符串比较是大小写无关；pg的则是大小写敏感的，pg实现大小写无关的字符串比较，可以使用lower函数，或者ILIKE等LastName = lastname“LastName” “lastname”mysql字段、表名等都可以大小写无关;pg 名字规范是只能用小写 ，不能用大写，如果一定要用大写字母，那么要加双引号。‘foo’ || ‘bar’‘foo’ or ‘bar’mysql支持||,&& 作为逻辑运算符；pg只支持and ,or这种标准SQL语法SELECT … LIMIT offset, limitSELECT … LIMIT limit OFFSET offsetSELECT … LIMIT limit OFFSET offset翻页语法LAST_INSERT_ID()CURRVAL(‘serial_variable’)mysql使用LAST_INSERT_ID()返回AUTO_INCREMENT生成的最后一个id 值; pg有更具可读性的方法，返回插入的数据列：INSERT INTO mytable VALUES (…) RETURNING my_serial_column_name;2.3 常用DDL语法差异用例子来说明：mysql 的建表语句: DROP TABLE IF EXISTS `telepg_node`;CREATE TABLE `telepg_node` ( `pg_node_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'PGSQL服务标识', `pg_inst_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'PGSQL实例标识', `pg_host_id` bigint(20) NULL DEFAULT NULL COMMENT 'PGSQL服务器标识', `install_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '安装路径', `data_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据路径', `script_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '脚本路径', `backup_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '备份路径', `service_port` int(11) NOT NULL COMMENT '服务端口', `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户标识', `tenant_id` bigint(20) NULL DEFAULT NULL COMMENT '租户标识', `zk_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'pg节点的zookeeper路径', `create_timestamp` datetime NULL DEFAULT NULL COMMENT '创建时间', `update_timestamp` datetime NULL DEFAULT NULL COMMENT '更新时间', `remark` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注', PRIMARY KEY (`pg_node_id`) USING BTREE, INDEX `idx_tenant_inst`(`tenant_id`,`pg_inst_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;转成postgresql： drop table if EXISTS telepg_node;CREATE TABLE "telepg_node" ( "pg_node_id" bigserial , "pg_inst_id" varchar(64) NOT NULL , "pg_host_id" bigint DEFAULT NULL , "install_path" varchar(256) NOT NULL , "data_path" varchar(256) NOT NULL , "script_path" varchar(256) NOT NULL , "backup_path" varchar(256) NOT NULL , "service_port" int NOT NULL , "user_id" bigint DEFAULT NULL , "tenant_id" bigint DEFAULT NULL , "zk_path" varchar(256) DEFAULT NULL , "create_timestamp" timestamp NULL DEFAULT NULL , "update_timestamp" timestamp NULL DEFAULT NULL , "remark" varchar(1024) DEFAULT NULL , primary key ("pg_node_id")) ;CREATE INDEX public_telepg_node_tenant_id0_idx ON public.telepg_node USING btree (tenant_id, pg_inst_id);COMMENT ON COLUMN "telepg_node". "pg_node_id" IS 'PGSQL服务标识';COMMENT ON COLUMN "telepg_node". "pg_inst_id" IS 'PGSQL实例标识';COMMENT ON COLUMN "telepg_node". "pg_host_id" IS 'PGSQL服务器标识';COMMENT ON COLUMN "telepg_node". "install_path" IS '安装路径';COMMENT ON COLUMN "telepg_node". "data_path" IS '数据路径';COMMENT ON COLUMN "telepg_node". "script_path" IS '脚本路径';COMMENT ON COLUMN "telepg_node". "backup_path" IS '备份路径';COMMENT ON COLUMN "telepg_node". "service_port" IS '服务端口';COMMENT ON COLUMN "telepg_node". "user_id" IS '用户标识';COMMENT ON COLUMN "telepg_node". "tenant_id" IS '租户标识';COMMENT ON COLUMN "telepg_node". "zk_path" IS 'pg节点的zookeeper路径';COMMENT ON COLUMN "telepg_node". "create_timestamp" IS '创建时间';COMMENT ON COLUMN "telepg_node". "update_timestamp" IS '更新时间';COMMENT ON COLUMN "telepg_node". "remark" IS '备注';简单来说，postgresql的ddl语句，不能直接带注释,也不需要指定字符编码。字符编码在create database时指定的。索引需要单独语句建.索引的名字要求唯一性，最好跟表名相关。 2.4 常用元数据查询差异mysqlpostgresqlpsql客户端快捷语法DESCRIBE tableselect column_name, data_type, character_maximum_lengthfrom INFORMATION_SCHEMA.COLUMNS where table_name = ‘t1’;\d tableSHOW DATABASESSELECT datname AS Database FROM pg_database WHERE datistemplate = ‘f’;\lSHOW TABLESSELECT c.relname AS Tables_in FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relkind = ‘r’ AND relname NOT LIKE ‘pg_%’;\dtpsql 是自带的命令行工具2.5 数据类型差异mysqlpostgresqlTINYINTSMALLINTSMALLINTSMALLINTMEDIUMINTINTEGERBIGINTBIGINTFLOATREALDOUBLEDOUBLE PRECISIONBOOLEANBOOLEANTINYTEXTTEXTTEXTTEXTMEDIUMTEXTTEXTLONGTEXTTEXTBINARY(n)BYTEAVARBINARY(n)BYTEATINYBLOBBYTEABLOBBYTEAMEDIUMBLOBBYTEALONGBLOBBYTEADATEDATETIMETIME [WITHOUT TIME ZONE]DATETIMETIMESTAMP [WITHOUT TIME ZONE]TIMESTAMPTIMESTAMP [WITHOUT TIME ZONE]AUTO_INCREMENTSERIAL , BIGSERIALcolumn ENUM (value1, value2, […]column VARCHAR(255) NOT NULL, CHECK (column IN (value1, value2, […])) pg可以自定义数据类型实现类似效果： CREATE TYPE mood AS ENUM ('sad','ok','happy'); CREATE TABLE person ( current_mood mood ... )2.4 常用元数据查询差异mysqlpostgresqlpsql客户端快捷语法DESCRIBE tableselect column_name, data_type, character_maximum_lengthfrom INFORMATION_SCHEMA.COLUMNS where table_name = ‘t1’;\d tableSHOW DATABASESSELECT datname AS Database FROM pg_database WHERE datistemplate = ‘f’;\lSHOW TABLESSELECT c.relname AS Tables_in FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relkind = ‘r’ AND relname NOT LIKE ‘pg_%’;\dtpsql 是自带的命令行工具2.5 语法差异&改造建议(1) LIMIT & OFFSET mysqlpostgresql备注LIMIT nLIMIT n偏移量0，取前n行数据。LIMIT m,nLIMIT n OFFSET m偏移量m，取n行数据。LIMIT n OFFSET mLIMIT n OFFSET m偏移量m，取n行数据。两者语法一致，兼容性好（2）字符串常量字符串常量MySQL可以用单引号或双引号包含, 但PostgreSQL只能用单引号，如：select * from t1 where t1.name = ‘a’; // MySQL和PostgreSQL都可以select * from t1 where t1.name = “a”; // PostgreSQL不可以 （3）插入数据时自增主键对于自增主键的value为null的插入语句，如insert into t1(id, name) values(null, ‘zhangshan’);MySQL可正确插入，主键成功自增，PostgreSQL则报错。所以PostgreSQL的插入语句，有2种写法：1）删除自增主键id字段：insert into t1(name) values(‘zhangshan’);2）显示提供自增主键id值：insert into t1(id, name) values(nextval(‘t1_id_seq’::regclass), ‘zhangshan’);推荐使用第1种写法，此写法兼容MySQL。请注意，如果客户端使用Mybatis ORM框架，当不指定自增主键值插入数据时，使用Dao层的insertSelective方法，该方法最终生成写法1的SQL语句。 （4）数据记录顺序记录被update后，在select不加order by的情况下：MySQL会按照id顺序排序记录。PostgreSQL不保证记录顺序，所以如果需要保证记录的顺序，需加order by。 (5) bool字段 一般mysql中用bit(1)表示布尔值bool，查询支持的参数为1、true或0、false。 PostgreSQL用bool表示bool值。查询支持的参数如下： 真TRUEtrue‘t’‘true’‘y’‘yes’‘1’假FALSEfalse‘f’‘false’‘n’‘no’‘0’Mybatis框架生成的对应POJO为Boolean值 兼容性实践，建议：查询参数传true或false PG也支持int自动转bool , 需要用超级用户执行SQL ： postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype; UPDATE 1这个修改是database级别的。参考文章： https://github.com/digoal/blog/blob/master/201801/20180131_01.md 参考A.参考阅读https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQLhttps://www.postgresql.org/docs/12/sql-createtable.html C. 一些MySQL 转PostgreSQL工具SQL 转化工具https://github.com/ahammond/mysql2pgsql.git (有部分语句无法正常转)https://www.convert-in.com/mysql-to-postgres.htm (收费软件) 数据迁移工具：https://github.com/AnatolyUss/nmig 文档更新时间: 2021-03-11 15:52 作者：詹溧 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |